#!/bin/bash
source /etc/profile

dt="`date -d "$d  0 days ago "  "+%Y-%m-%d"`"
mysql -uroot -proot -hhadoop2<<EOF
use oracleanditem;
 SELECT 
 a.EFFECTIVEDATE,a.count,sum(b.count) 
 from (SELECT EFFECTIVEDATE,COUNT(CARD_ID)AS COUNT 
 FROM TBL_CARD GROUP BY EFFECTIVEDATE )as a  
 CROSS join 
(SELECT EFFECTIVEDATE,COUNT(CARD_ID)AS COUNT 
 FROM TBL_CARD GROUP BY EFFECTIVEDATE )as b
 where a.EFFECTIVEDATE>=b.EFFECTIVEDATE 
 GROUP BY a.EFFECTIVEDATE,a.COUNT;
EOF
#awk ' NR !=1 {print "('\''"$1,$2"'\'',","'\''"$3"'\'',","'\''"$4"'\'',",$5,","$6,","$7")"}' mysql.log >tmp.log
#awk ' NR !=1 {print "INSERT into test.res_oil VALUES('\''"$1,$2"'\'',","'\''"$3"'\'',","'\''"$4"'\'',",$5,","$6,","$7") on duplicate key update oilNumber="$5",oilLitre="$6",oilMoney="$7";"}' mysql.log >insert.sql
